﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
    public partial class frm_mainentry : Form
    {
        public string data;
        OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "/mainentry.accdb");
        OleDbConnection connparty = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "/PartyDetail.accdb");
        string vat;
        private BindingSource bindingSource1 = new BindingSource();
        DataGridViewButtonColumn editButton;
        DataGridViewButtonColumn deleteButton;
        OleDbCommand cmd;
        DataTable party = new DataTable();
        int mainid;
        string query;
        string command = "";
        public frm_mainentry()
        {
            InitializeComponent();
        }
        public void getposition()
        {
            data = "xyz";
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            btn_save.Enabled = false;
            GetData();
            ClearLabel();
            txt_percent.Text = "5";
            rowfontsizechange(dataGridView1);
            rowfontsizechange(dataGridView2);
            int x = Convert.ToInt32(txt_partyname.Location.X);
            int y = Convert.ToInt32(txt_partyname.Location.Y + txt_partyname.Height);
            this.dataGridView2.Location = new System.Drawing.Point(x, y);
            dataGridView2.Visible = false;

        }
      

        public void rowfontsizechange(DataGridView r)
        {
            foreach (DataGridViewRow row in r.Rows)
            {
                row.Height = 25;
                row.DefaultCellStyle.Font = new Font("VDGGJ", 16);
            }
        }


        public void ClearLabel()
        {

            foreach (Control child in Controls)
            {
                if (child is TextBox)
                {
                    var textBox = (TextBox)child;
                    textBox.Font = new Font("VDGGJ", 16);
                }
                if (child is Label)
                {
                    child.ForeColor = Color.Blue;
                }
            }

        }
        public void disable()
        {
            dt_billdate.Enabled = false;
            txt_id.Enabled = false;
            txt_partyid.Enabled = false;
            txt_partyname.Enabled = false;
            txt_billamount.Enabled = false;
            //txt_item1.Enabled=false;
            txt_percent.Enabled = false;
            txt_taxfree.Enabled = false;
        }
        public void ClearData()
        {
            //dt_billdate.=false;
            txt_id.Text = "";
            txt_partyid.Text = "";
            txt_partyname.Text = "";
            txt_billamount.Text = "";
            //txt_item1.Text="";
            txt_percent.Text = "";
            txt_taxfree.Text = "";
        }
        private void label8_Click(object sender, EventArgs e)
        {

        }

        private void btn_update_Click(object sender, EventArgs e)
        {

        }

        private void txt_partyid_TextChanged(object sender, EventArgs e)
        {

        }

        private void btn_exit_Click(object sender, EventArgs e)
        {
            this.Close();
            frm_Startup fstar = new frm_Startup();
            fstar.Show();
        }

        private void txt_partyid_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                txt_item.Enabled = true;
                txt_item.Focus();
            }
            if (e.KeyChar == 32)
            {

                string gridquery = "select partyid,firmname from detail";
                loadpartygrid(gridquery);
                rowfontsizechange(dataGridView2);
                dataGridView2.Refresh();
                dataGridView2.Visible = true;
                txt_partyname.Enabled = true;
                txt_partyname.Focus();
                txt_partyname.Text = "";
            }
            NumberOnly(txt_partyid, e);
        }
        public void NumberOnly(object sender, KeyPressEventArgs e)
        {
            if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar) && e.KeyChar != '.')
            {
                e.Handled = true;
            }

            // only allow one decimal point
            //if (e.KeyChar == '.'
            //    && (sender as TextBox).Text.IndexOf('.') > -1)
            //{
            //    e.Handled = true;
            //}
        }
        private void txt_item_TextChanged(object sender, EventArgs e)
        {

        }

        private void txt_item_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                txt_billamount.Enabled = true;
                txt_billamount.Focus();
            }
        }

        private void txt_billamount_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                txt_taxfree.Enabled = true;
                txt_taxfree.Focus();
            }
            NumberOnly(txt_partyid, e);
        }

        private void txt_taxfree_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                txt_percent.Enabled = true;
                txt_percent.Focus();
            }
        }

        private void txt_percent_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                btn_save.Enabled = true;
                btn_save.Focus();
            }
        }

        private void txt_percent_Leave(object sender, EventArgs e)
        {
            double val;
            if (txt_taxfree.Text == "")
            {
                txt_taxfree.Text = "0";
            }
            val = Convert.ToDouble(txt_billamount.Text) - Convert.ToDouble(txt_taxfree.Text);

            if (txt_percent.Text == "15")
            {
                lbl_per1.Text = ((val * 12.5) / 100).ToString();
                lbl_per1.Visible = true;
                lbl_per2.Text = ((val * 2.5) / 100).ToString();
                lbl_per2.Visible = true;
            }
            else if (txt_percent.Text == "5")
            {
                lbl_per1.Text = ((val * 4) / 100).ToString();
                lbl_per1.Visible = true;
                lbl_per2.Text = ((val * 1) / 100).ToString();
                lbl_per2.Visible = true;
            }
            else
            {
                MessageBox.Show("PLEASE ENTER RIGHT PERCENTAGE");
            }

        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            ClearData();
            dt_billdate.Enabled = true;
            dt_billdate.Focus();
            btn_add.Enabled = false;
            //btn_update.Enabled = false;
            btn_save.Enabled = false;
            //btn_delete.Enabled = false;
        }

        private void dt_billdate_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                txt_partyid.Enabled = true;
                txt_partyid.Focus();
            }
        }

        private void dt_billdate_Leave(object sender, EventArgs e)
        {
            try
            {
                conn.Open();
                string query = "select max(mainid) from mainentry where billdate='" + dt_billdate.Value.ToString("dd/MM/yyyy") + "'";
                string select = "SELECT * FROM mainentry where billdate='" + dt_billdate.Value.ToString("dd/MM/yyyy") + "'";
                loadDataGrid(select);
                OleDbCommand cmd = new OleDbCommand(query, conn);
                cmd.ExecuteScalar();
                string abc = cmd.ExecuteScalar().ToString();
                //MessageBox.Show(abc);
                if (cmd.ExecuteScalar().ToString() == "")
                {
                    txt_id.Text = "1";
                }
                else
                {
                    txt_id.Text = (Convert.ToInt64(cmd.ExecuteScalar().ToString()) + 1).ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                conn.Close();
            }

        }
        public void GetData()
        {
            try
            {
                string select = "SELECT * FROM mainentry where billdate='" + dt_billdate.Value.ToString("dd/MM/yyyy") + "'";
                loadDataGrid(select);
                //Connection c = new Connection();
                //OleDbDataAdapter dataAdapter = new OleDbDataAdapter(select, conn); //c.con is the connection string

                //OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
                //DataSet ds = new DataSet();
                //dataAdapter.Fill(ds);
                //dataGridView1.ReadOnly = true;
                //dataGridView1.DataSource = ds;
                //dataGridView1.Refresh();
                //// Resize the DataGridView columns to fit the newly loaded content.
                //dataGridView1.AutoResizeColumns(
                //    DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                conn.Close();
            }

        }
        public void loadDataGrid(string sqlQueryString)
        {

            OleDbCommand SQLQuery = new OleDbCommand();
            DataTable data = null;
            dataGridView1.DataSource = null;
            SQLQuery.Connection = null;
            OleDbDataAdapter dataAdapter = null;
            dataGridView1.Columns.Clear(); // <-- clear columns
            //---------------------------------
            SQLQuery.CommandText = sqlQueryString;
            SQLQuery.Connection = conn;
            data = new DataTable();
            dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
            dataGridView1.DataSource = data;
            dataGridView1.AllowUserToAddRows = false; // remove the null line
            dataGridView1.ReadOnly = true;
            dataGridView1.Columns[0].Visible = false;
            dataGridView1.Columns[1].Width = 50;
            dataGridView1.Columns[2].Width = 200;
            dataGridView1.Columns[3].Width = 100;
            dataGridView1.Columns[4].Width = 80;
            dataGridView1.Columns[5].Visible = false;
            dataGridView1.Columns[6].Visible = false;
            dataGridView1.Columns[7].Visible = false;
            dataGridView1.Columns[8].Visible = false;
            dataGridView1.Columns[9].Visible = false;
            dataGridView1.Columns[12].Visible = false;
            dataGridView1.Columns[13].Visible = false;
            dataGridView1.Columns[14].Visible = false;
            //dataGridView1.Columns[15].Visible = false;
            // insert edit button into datagridview
            editButton = new DataGridViewButtonColumn();
            editButton.HeaderText = "EDIT";
            editButton.Text = "suFaro";
            editButton.UseColumnTextForButtonValue = true;
            editButton.Width = 80;
            dataGridView1.Columns.Add(editButton);
            // insert delete button to datagridview
            deleteButton = new DataGridViewButtonColumn();
            deleteButton.HeaderText = "DELETE";
            deleteButton.Text = "kaQva";
            deleteButton.UseColumnTextForButtonValue = true;
            deleteButton.Width = 80;
            dataGridView1.Columns.Add(deleteButton);
            rowfontsizechange(dataGridView1);
        }
        public void loadpartygrid(string sqlQueryString)
        {

            OleDbCommand SQLQuery = new OleDbCommand();
            DataTable data = null;
            dataGridView2.DataSource = null;
            SQLQuery.Connection = null;
            OleDbDataAdapter dataAdapter = null;
            dataGridView2.Columns.Clear(); // <-- clear columns
            //---------------------------------
            SQLQuery.CommandText = sqlQueryString;
            SQLQuery.Connection = connparty;
            data = new DataTable();
            dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
            dataGridView2.DataSource = data;
            dataGridView2.AllowUserToAddRows = false; // remove the null line
            dataGridView2.ReadOnly = false;
            //dataGridView1.Columns[0].Visible = false;
            //dataGridView1.Columns[1].Width = 50;
            //dataGridView1.Columns[2].Width = 200;
            //dataGridView1.Columns[3].Width = 100;
            //dataGridView1.Columns[4].Width = 80;
            //dataGridView1.Columns[5].Visible = false;
            //dataGridView1.Columns[6].Visible = false;
            //dataGridView1.Columns[7].Visible = false;
            //dataGridView1.Columns[8].Visible = false;
            //dataGridView1.Columns[9].Visible = false;
            //dataGridView1.Columns[12].Visible = false;
            //dataGridView1.Columns[13].Visible = false;
            //dataGridView1.Columns[14].Visible = false;
            //dataGridView1.Columns[15].Visible = false;
            //// insert edit button into datagridview
            //editButton = new DataGridViewButtonColumn();
            //editButton.HeaderText = "EDIT";
            //editButton.Text = "suFaro";
            //editButton.UseColumnTextForButtonValue = true;
            //editButton.Width = 80;
            //dataGridView1.Columns.Add(editButton);
            //// insert delete button to datagridview
            //deleteButton = new DataGridViewButtonColumn();
            //deleteButton.HeaderText = "DELETE";
            //deleteButton.Text = "kaQva";
            //deleteButton.UseColumnTextForButtonValue = true;
            //deleteButton.Width = 80;
            //dataGridView1.Columns.Add(deleteButton);
            rowfontsizechange(dataGridView2);
            dataGridView2.Refresh();
        }
        private void dt_billdate_ValueChanged(object sender, EventArgs e)
        {

        }

        private void btn_save_Click(object sender, EventArgs e)
        {
            
            DateTime dt = new DateTime();
            dt = DateTime.Now;
            try
            {

                conn.Open();


                // MessageBox.Show(dt.ToString("dd/MM/yyyy"));
                // string query = "insert into mainentry values(" + txt_id.Text + "," + txt_partyid.Text + ",'" + txt_partyname.Text + "'," + txt_billamount.Text + "," + txt_taxfree.Text + "," + vat + ",'" + dt_billdate.Value.ToString("dd/MM/yyyy") + "','" + dt.ToString("dd/MM/yyyy") + "','" + txt_item.Text + "',15," + lbl_per1.Text + "," + lbl_per2.Text + ",13,14,15)";
                if (command == "Edit")
                {
                    int percent = Convert.ToInt32(txt_percent.Text);
                    query = "update mainentry set partyid=@d1,partyname=@d2,billamount=@d3,taxfree=@d4,per1=@d6,per2=@d7,percent1="+ percent +" where mainid=@d9";
                    cmd = new OleDbCommand(query, conn);
                    cmd.Parameters.AddWithValue("@d1", txt_partyid.Text);
                    cmd.Parameters.AddWithValue("@d2", txt_partyname.Text);
                    cmd.Parameters.AddWithValue("@d3", txt_billamount.Text);
                    cmd.Parameters.AddWithValue("@d4", txt_taxfree.Text);
                   // cmd.Parameters.AddWithValue("@d5", txt_percent.Text);
                    cmd.Parameters.AddWithValue("@d6", lbl_per1.Text);
                    cmd.Parameters.AddWithValue("@d7", lbl_per2.Text);
                    //cmd.Parameters.AddWithValue("@d8", txt_item.Text);
                    cmd.Parameters.AddWithValue("@d9", txt_id.Text);
                    MessageBox.Show(query);
                }
                else
                {
                    query = "insert into mainentry values(" + txt_id.Text + "," + txt_partyid.Text + ",'" + txt_partyname.Text + "'," + txt_billamount.Text + "," + txt_taxfree.Text + ",'" + vat + "','" + dt_billdate.Value.ToString("dd/MM/yyyy") + "','" + dt.ToString("dd/MM/yyyy") + "','" + txt_item.Text + "','" + txt_percent.Text + "'," + lbl_per1.Text + "," + lbl_per2.Text + ",13,14,15)";
                    cmd = new OleDbCommand(query, conn);
                }
                //string query = "("+ txt_id.Text +","+ txt_partyid.Text +",'"+ txt_partyname.Text  +"',"+  txt_billamount.Text  +","+ txt_taxfree.Text  +","+ vat +","+ dt_billdate.Value +","+ dt +","+ txt_item.Text +","+ txt_percent.Text +","+ lbl_per1.Text +","+ lbl_per2.Text +",a,b,c)";
                //MessageBox.Show(query);

                cmd.ExecuteNonQuery();
                disable();
                btn_save.Enabled = false;
                btn_add.Enabled = true;
                //btn_delete.Enabled = true;
                //btn_update.Enabled = true;
                btn_add.Focus();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                conn.Close();
                command = "";
                string select = "SELECT * FROM mainentry where billdate='" + dt_billdate.Value.ToString("dd/MM/yyyy") + "'";
                loadDataGrid(select);
            }
        }

        private void txt_percent_TextChanged(object sender, EventArgs e)
        {

        }

        private void txt_item_Enter(object sender, EventArgs e)
        {
            DataTable data = null;

            //using (var cn = new SqlConnection(CONNECTION_STRING))

            conn.Open();

            try
            {
                OleDbCommand SQLQuery = new OleDbCommand();

                txt_item.DataSource = null;
                SQLQuery.Connection = null;
                OleDbDataAdapter dataAdapter = null;
                txt_item.Items.Clear(); // <-- clear columns
                //---------------------------------
                SQLQuery.CommandText = "select itemname from mainentry group by itemname order by itemname";
                SQLQuery.Connection = conn;
                data = new DataTable();
                dataAdapter = new OleDbDataAdapter(SQLQuery);
                dataAdapter.Fill(data);
                txt_item.DataSource = data;

            }
            catch (OleDbException ex)
            {
                throw (ex);
            }
            finally
            {
                conn.Close();
            }
            txt_item.ValueMember = "itemname";
            txt_item.DisplayMember = "itemname";
            if (data.Rows.Count > 0)
            {
                txt_item.Text = data.Rows[0][0].ToString();
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            command = "Edit";
            string queryString = "SELECT * FROM mainentry where billdate='" + dt_billdate.Value.ToString("dd/MM/yyyy") + "'";

            int currentRow = int.Parse(e.RowIndex.ToString());
            try
            {
                conn.Open();
                string mainidedit = dataGridView1[0, currentRow].Value.ToString();
                mainid = int.Parse(mainidedit);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                // edit button
                if (dataGridView1.Columns[e.ColumnIndex] == editButton && currentRow >= 0)
                {


                    string billdate = dataGridView1[6, currentRow].Value.ToString();
                    string id = dataGridView1[0, currentRow].Value.ToString();
                    string partyid = dataGridView1[1, currentRow].Value.ToString();
                    string partyname = dataGridView1[2, currentRow].Value.ToString();
                    string itemname = dataGridView1[8, currentRow].Value.ToString();
                    string billamount = dataGridView1[3, currentRow].Value.ToString();
                    string vat = dataGridView1[5, currentRow].Value.ToString();
                    string percent = dataGridView1[9, currentRow].Value.ToString();
                    string per1 = dataGridView1[10, currentRow].Value.ToString();
                    string per2 = dataGridView1[11, currentRow].Value.ToString();
                    //string year = dataGridView1[4, currentRow].Value.ToString();
                    string tax_free = dataGridView1[4, currentRow].Value.ToString();
                    string ex1 = dataGridView1[12, currentRow].Value.ToString();
                    string ex2 = dataGridView1[13, currentRow].Value.ToString();
                    string ex3 = dataGridView1[14, currentRow].Value.ToString();
                    string sysdate = dataGridView1[7, currentRow].Value.ToString();

                    txt_id.Text = id;
                    txt_partyid.Text = partyid;
                    txt_partyname.Text = partyname;
                    txt_item.Text = itemname;
                    txt_billamount.Text = billamount;
                    txt_percent.Text = percent;
                    lbl_per1.Text = per1;
                    lbl_per2.Text = per2;
                    txt_taxfree.Text = tax_free;
                    lbl_per1.Visible = true;
                    lbl_per2.Visible = true;



                    dataGridView1.Update();
                    txt_partyid.Enabled = true;
                    txt_partyid.Focus();
                }
                // delete button
                else if (dataGridView1.Columns[e.ColumnIndex] == deleteButton && currentRow >= 0)
                {
                    // delete sql query
                    string queryDeleteString = "DELETE FROM mainentry where mainid = " + mainid + "";
                    OleDbCommand sqlDelete = new OleDbCommand();
                    sqlDelete.CommandText = queryDeleteString;
                    sqlDelete.Connection = conn;
                    sqlDelete.ExecuteNonQuery();
                    loadDataGrid(queryString);
                }
                conn.Close();
            }
        }

        private void txt_billamount_Leave(object sender, EventArgs e)
        {
            if (txt_billamount.Text == "")
            {
                MessageBox.Show("PLEASE ENTER BILL AMOUNT");
                txt_billamount.Focus();
            }
        }
        private void txt_partyid_Leave(object sender, EventArgs e)
        {
            if (dataGridView2.Visible == false)
            {
                if (txt_partyid.Text == "" || txt_partyid.Text == " ")
                {
                    MessageBox.Show("PLEASE ENTER PARTY ID");
                    txt_partyid.Focus();
                }
                else
                {
                    try
                    {
                        //connparty.Open();
                        party.Clear();
                        string query = "select * from detail where partyid=" + txt_partyid.Text + "";
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.CommandText = query;
                        cmd.Connection = connparty;

                        OleDbDataAdapter partyAdapter = new OleDbDataAdapter(cmd);
                        partyAdapter.Fill(party);
                        if (party.Rows.Count > 0)
                        {
                            txt_partyname.Text = party.Rows[0][1].ToString();
                            vat = party.Rows[0][16].ToString();
                        }
                        else
                        {
                            MessageBox.Show("PLEASE SELECT RIGHT PARTY....");
                            txt_partyid.Focus();
                        }

                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        //conn.Close();
                    }

                }

            }

        }

        private void txt_partyname_TextChanged(object sender, EventArgs e)
        {
            string Query = "select partyid,firmname from detail where Firmname like '" + txt_partyname.Text + "%'";
            loadpartygrid(Query);
            //.Visible = true;
        }

        private void txt_partyname_KeyPress(object sender, KeyPressEventArgs e)
        {

        }

        private void txt_partyname_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Down)
            {
                if (dataGridView2.Visible == true)
                {
                    dataGridView2.Focus();
                }
            }
        }

        private void dataGridView2_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyData == Keys.Enter)
            {
                e.Handled = true;
            }
        }

        private void dataGridView2_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                int i = dataGridView2.CurrentRow.Index;
                string val = dataGridView2[0, i].Value.ToString();
                txt_partyid.Text = val;
                dataGridView2.Visible = false;
                txt_partyid.Focus();

            }
        }

        private void txt_percent_SelectedIndexChanged(object sender, EventArgs e)
        {
            double val;
            if (txt_taxfree.Text == "")
            {
                txt_taxfree.Text = "0";
            }
            if (txt_billamount.Text == "")
            {
                txt_billamount.Text = "0";
            }
            val = Convert.ToDouble(txt_billamount.Text) - Convert.ToDouble(txt_taxfree.Text);

            if (txt_percent.Text == "15")
            {
                lbl_per1.Text = ((val * 12.5) / 100).ToString("0.00");
                lbl_per1.Visible = true;
                lbl_per2.Text = ((val * 2.5) / 100).ToString("0.00");
                lbl_per2.Visible = true;
            }
            else if (txt_percent.Text == "5")
            {
                lbl_per1.Text = ((val * 4) / 100).ToString("0.00");
                lbl_per1.Visible = true;
                lbl_per2.Text  =((val * 1) / 100).ToString("0.00");
                lbl_per2.Visible = true;
            }
            else
            {
                MessageBox.Show("PLEASE ENTER RIGHT PERCENTAGE");
            }

        }

        private void btn_party_Click(object sender, EventArgs e)
        {
            label11.Text = "ok";
            frm_partyMaster frm2 = new frm_partyMaster();
            frm2.ModifyTextBoxValue = label11.Text;
            frm_partyMaster frmparty = new frm_partyMaster();
            frmparty.ShowDialog();
           
        }

        private void txt_billamount_TextChanged(object sender, EventArgs e)
        {

        }

        private void label7_Click(object sender, EventArgs e)
        {

        }
    }

}


